package com.yourname.www.dao;

import com.yourname.www.entity.*;
import com.yourname.www.util.Tools;

import java.math.BigInteger;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class JdbcHelper implements JdbcConfig{
    //定义连接数据库所需要的对象
    //预处理对象
    private PreparedStatement ps = null;
    //查询结果集
    private ResultSet rs = null;
    //数据库连接
    private Connection ct = null;
    //获得数据库的连接
    private  void init(){
        try {
            Class.forName(DRIVER);
            ct = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 获得数据库连接
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    //无参构造函数
    public JdbcHelper(){
        this.init();
    }
    //关闭数据库资源
    public void close()	{
        try {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(ct!=null) ct.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //验证用用户名是否已被注册
    public boolean checkUserIsExists(User user){
        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select * from tb_user where username=?");
            //对参数进行赋值【注意数据类型】
            ps.setString(1, user.getUsername());
            //执行查询语句
            rs = ps.executeQuery();
            //表示用户名是存在
            if(rs.next()){
                return false;
            }
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return false;

    }
    //写入注册用户信息
    public boolean insertRegisterUser(User user){
        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("insert into tb_User(username,password) values(?,?)");
            //对参数进行赋值【注意数据类型】
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            //执行写入操作 返回的是成功写入数据库的行数
            int update = ps.executeUpdate();
            //表示用户名是存在
            if(update>0){
                return true;
            }
            return false;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return false;
    }

    /**
     * 根据用户名查找
     * @param user
     * @return
     */
    public User getUserInfoByUserName(User user) {
        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select * from tb_user where username=?");
            //对参数进行赋值【注意数据类型】
            ps.setString(1, user.getUsername());
            //执行查询语句
            rs = ps.executeQuery();

            //表示用户名是存在
            if(rs.next()){
                //
               User user1 = new User();
               user1.setUsername(rs.getString("username"));
               user1.setPassword(rs.getString("password"));
               user1.setIsLogin(rs.getInt("is_login"));
               user1.setId(rs.getInt("id"));
               return user1;
            }
            return null;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return null;

    }

    /**
     * 登录成功后，更新用户数据表
     * @param user
     * @return
     */
    public boolean updateWhenLoginSuccess(User user) {

        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("update tb_User SET is_login=1,update_time=? where id=?");
            //对参数进行赋值【注意数据类型】
            // 获取当前时间
            Date date = new Date();
            // 创建SimpleDateFormat对象并指定日期格式
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 将日期按照指定格式转换成字符串
            String formattedDate = sdf.format(date);

            ps.setString(1,formattedDate);
            ps.setInt(2, user.getId());
            //执行写入操作 返回的是成功写入数据库的行数
            //受影响的行数
            int update = ps.executeUpdate();
            //表示用户名是存在
            if(update == 1){
                //更新成功
                return true;
            }
            return false;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return false;
    }

    /**
     * 获取所有院系名
     * @return
     */
    public List<Department> getAllDepartment(){
        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select * from tb_department order by department_id asc");
            //执行查询语句
            rs = ps.executeQuery();

            //表示用户名是存在
            //
            List<Department> departmentList = new ArrayList<>();
            while(rs.next()){
                Department department = new Department();
                department.setDepartment_ID(rs.getString("Department_ID"));
                department.setDepartment_Name(rs.getString("Department_Name"));
                departmentList.add(department);
            }
            return departmentList;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return null;

    }

    /**
     * 根据院系名称查找对应的专业
     * @param departmentName
     * @return
     */
    public List<Major> getMajorByDepartmentName(String departmentName){
        String departmentId = getDepartmentIdByDepartmentName(departmentName);
        try {

            if(departmentId !=null){
                departmentId = rs.getString("Department_ID");
                //根据ID查询专业表
                ps = ct.prepareStatement("select * from tb_major WHERE Department_ID=?");
                ps.setString(1,departmentId);
                rs = ps.executeQuery();
                List<Major> majorList = new ArrayList<>();
                while(rs.next()){
                    Major major = new Major();
                    major.setDepartment_ID(rs.getString("Department_ID"));
                    major.setMajor_Name(rs.getString("Major_Name"));
                    major.setMajor_ID(rs.getString("Major_ID"));
                    majorList.add(major);
                }
                return majorList;


            }
            return null;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return null;

    }

    /**
     * 根据年级和专业名称查询班级表
     * @param grade
     * @param majorName
     * @return
     */
    public List<Classe> getClasseByGradeAndMajorID(int grade,String majorName){
        //先根据专业名称获取专业ID
        String majorId = getMajorIdByMajorName(majorName);




        try {

            if(majorId==null){
                return null;
            }
            System.out.println("majorId:" + majorId);
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select * from tb_classe where Grade=? and Major_ID=?");
            //执行查询语句
            ps.setInt(1,grade);
            ps.setString(2,majorId);
            rs = ps.executeQuery();
            System.out.println("Select * from tb_classe where Grade=? and Major_ID=?"+majorId+grade);
            //表示用户名是存在
            //
            List<Classe> classeList = new ArrayList<>();
            while(rs.next()){
                Classe classe = new Classe();
                classe.setGrade(rs.getInt("Grade"));
                classe.setMajor_ID(rs.getString("Major_ID"));
                classe.setClasse(rs.getInt("Classe"));
                classeList.add(classe);
            }
            return classeList;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return null;

    }

    /**
     *根据专业名称获取专业名称
     * @param majorName
     * @return
     */
    public String getMajorIdByMajorName(String majorName){
        String majorId = null;

        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select Major_ID from tb_major WHERE Major_Name=?");
            //执行查询语句
            ps.setString(1,majorName);
            System.out.println("select Major_ID from tb_major WHERE Major_Name="+majorName);
            rs = ps.executeQuery();
            if(rs.next()){
                majorId = rs.getString("Major_ID");
                return  majorId;
            }else{
                return null;
            }
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return null;
    }

    /**
     * 根据院系名称获取院系ID
     * @param departmentName
     * @return
     */
    public String getDepartmentIdByDepartmentName(String departmentName){
        String departmentId = null;
        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select Department_ID from tb_department WHERE Department_Name=?");
            //执行查询语句
            ps.setString(1,departmentName);
            rs = ps.executeQuery();
            //表示用户名是存在
            if(rs.next()){
                departmentId = rs.getString("Department_ID");
                return departmentId;
            }
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return null;
    }

    /***
     * 添加学生
     * @param student
     * @return
     */
    public boolean insertStudent(Student student) {


        try {

            //对参数进行赋值【注意数据类型】
            student.setMajor_ID(getMajorIdByMajorName(student.getMajor_Name()));
            student.setDepartment_ID(getDepartmentIdByDepartmentName(student.getDepartment_Name()));
            student.setStudent_Id(getMaxNum(student));
            System.out.println(student.getStudent_Id());
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("insert into tb_student(Student_ID,Student_Name,Student_Sex,Grade,Classe,Major_ID,Major_Name,Department_ID,Department_Name)" +
                    " values(?,?,?,?,?,?,?,?,?)");
            ps.setString(1,student.getStudent_Id());//23 3  11   2    123456
            ps.setString(2, student.getStudent_Name());
            ps.setString(3, student.getStudent_Sex());
            ps.setInt(4, student.getGrade());
            ps.setInt(5, student.getClasse());
            ps.setString(6, student.getMajor_ID());//
            ps.setString(7, student.getMajor_Name());
            ps.setString(8, student.getDepartment_ID());//
            ps.setString(9, student.getDepartment_Name());
            //执行写入操作 返回的是成功写入数据库的行数
            int update = ps.executeUpdate();
            //表示用户名是存在
            if(update ==1){
                return true;
            }
            return false;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return false;


    }

    /**
     * 计算学号
     * @param student
     * @return
     */
    private String getMaxNum(Student student) {
        String maxIdPre = ""+student.getGrade()+student.getDepartment_ID()+student.getMajor_ID()+student.getClasse();
        try {
            //预处理要执行的SQL预计，参数用?站位
            ps = ct.prepareStatement("select Student_ID from tb_student WHERE grade=? and department_id=? and major_id=? and classe = ? ORDER BY Student_ID desc LIMIT 1");
            //执行查询语句
            ps.setInt(1,student.getGrade());
            ps.setString(2,student.getDepartment_ID());
            ps.setString(3,student.getMajor_ID());
            ps.setInt(4,student.getClasse());
            rs = ps.executeQuery();
            //表示该年级该院系该专业该班级已有学生
            if(rs.next()){
                String maxId =  rs.getString("Student_ID");
                String number = maxId.substring(maxId.length()-2);
                return maxIdPre+String.format( "%2s", (Integer.valueOf(number) + 1)).replace(" ","0");
            }
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return maxIdPre+"01";

    }

    /***
     * 根据查询条件查找学生信息
     * @param where
     * @return
     */
    public List<Student> queryStudentList(Map<String, String> where) {
        try {
            //预处理要执行的SQL预计，参数用?站位
            //"+22"+“”+“”+“”+""
            StringBuilder whereStrMore = new StringBuilder("WHERE 1=1 ");
            StringBuilder whereStrOne = new StringBuilder();
            where.forEach((k,v)->{
                if("all".equals(k)){
                    whereStrOne.append(" WHERE Student_Id Like '").append(v).append("%'");
                    whereStrOne.append(" OR Student_Name Like '").append(v).append("%'");
                    whereStrOne.append(" OR Student_Sex Like '").append(v).append("%'");
                    whereStrOne.append(" OR Grade Like '").append(v).append("%'");
                    whereStrOne.append(" OR Classe Like '").append(v).append("%'");
                    whereStrOne.append(" OR Major_Name Like '").append(v).append("%'");
                    whereStrOne.append(" OR Department_Name Like '").append(v).append("%'");
                }else{
                    //只要查询的值是字符串，需要单引号
                    whereStrMore.append(" AND ").append(k).append("='").append(v).append("'");
                }
            });
           if(whereStrOne.length()>0){
                ps = ct.prepareStatement("select * from tb_student "+whereStrOne+" ORDER BY Student_ID ASC ");
                System.out.println("select * from tb_student \"+whereStrOne+\" ORDER BY Student_ID ASC");
            }else{
                ps = ct.prepareStatement("select * from tb_student "+whereStrMore+" ORDER BY Student_ID ASC ");
                System.out.println("select * from tb_student "+whereStrMore+" ORDER BY Student_ID ASC ");
            }


            //执行查询语句
            rs = ps.executeQuery();
            //表示该年级该院系该专业该班级已有学生
            List<Student> studentList = new ArrayList<>();
            while(rs.next()){
                Student student = new Student();
                student.setStudent_Id(rs.getString("Student_Id"));
                student.setStudent_Name(rs.getString("Student_Name"));
                student.setStudent_Sex(rs.getString("Student_Sex"));
                student.setGrade(rs.getInt("Grade"));
                student.setClasse(rs.getInt("Classe"));
                student.setMajor_ID(rs.getString("Major_ID"));
                student.setMajor_Name(rs.getString("Major_Name"));
                student.setDepartment_ID(rs.getString("Department_ID"));
                student.setDepartment_Name(rs.getString("Department_Name"));
                studentList.add(student);

            }
            return  studentList;
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return new ArrayList<>();

    }
}
